﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ClientRegister
{
    public class StudentInClassDataProvider
    {
        ConnectDB connect = new ConnectDB();
        SqlConnection cnn;
        SqlCommand cmd;
        SqlDataAdapter ada;
        SqlDataReader sdr;

        public int InsertStudentInClass(string IdStudent, string IdClass)
        {
            cnn = connect.Connect();
            cnn.Open();
            cmd = new SqlCommand("insert into StudentInClass(IDStudent,IDClass,NewRecord,CreateDateTime) values(@IDStudent, @IDClass,@record,@CreateDateTime)", cnn);
            cmd.Parameters.AddWithValue("@IDStudent", IdStudent);
            cmd.Parameters.AddWithValue("@IDClass", IdClass);
            cmd.Parameters.AddWithValue("@record", 1);
            cmd.Parameters.AddWithValue("@CreateDateTime", DateTime.Now.ToString());
            
            return cmd.ExecuteNonQuery();
        }

        public int changeClassForStudent(string idStu, string OldClass, string NewClass, string Note)
        {
            Service.RegisterService gettime = new Service.RegisterService();
            string currTime = gettime.currentTime();
            string query = string.Format("Update StudentInClass set IDClass = '{2}',OldIdClass = '{1}', Note = '{4}', NewRecord = 2, LastUpdate = '{3}' where IDStudent ='{0}' and IDClass = '{1}'", idStu, OldClass, NewClass,DateTime.Parse(currTime),Note);
            cnn = connect.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            return cmd.ExecuteNonQuery();
        }

        public SqlDataReader selectClassForStuInfo(string idStu, string IdClass)
        {
            string query = string.Format("select * from StudentInClass where IDStudent = '{0}' and IDClass = '{1}'", idStu, IdClass);
            cnn = connect.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            sdr = cmd.ExecuteReader();
            return sdr;
        }

        public string selectClassOfStu(string idStu, string idClass)
        {
            string query = string.Format("select * from StudentInClass where IDStudent = '{0}' and IDClass = '{1}' for xml auto,elements", idStu, idClass);
            string xmlstring = "";
            cnn = connect.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);

            xmlstring = cmd.ExecuteScalar().ToString();
            return xmlstring;
        }

        //Lay id cua cot co record = 1
        public DataSet SelectIDNewRecord()
        {
            string query = string.Format("Select IDStudent,IDClass from StudentInClass where NewRecord = 1");
            cnn = connect.Connect();
            DataSet ds = new DataSet();
            try
            {
                cnn.Open();
                cmd = new SqlCommand(query, cnn);
                ada = new SqlDataAdapter(cmd);
                ada.Fill(ds);
                return ds;
            }
            catch (Exception) { throw; }
            finally
            {
                if (cnn != null && cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
        }
        //Cap nhat lai cot newrecord sau khi dua len server
        public void UpdateNewRecord()
        {
            string query = string.Format("update StudentInClass set NewRecord = 0 where NewRecord = 1");
            cnn = connect.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            cmd.ExecuteNonQuery();
        }

        //Lay id cua cot co record = 2
        public DataSet SelectIDNewRecordUpdate()
        {
            string query = string.Format("Select IDStudent,IDClass from StudentInClass where NewRecord = 2");
            cnn = connect.Connect();
            DataSet ds = new DataSet();
            try
            {
                cnn.Open();
                cmd = new SqlCommand(query, cnn);
                ada = new SqlDataAdapter(cmd);
                ada.Fill(ds);
                return ds;
            }
            catch (Exception) { throw; }
            finally
            {
                if (cnn != null && cnn.State == ConnectionState.Open)
                    cnn.Close();
            }
        }
        //Cap nhat lai cot newrecord sau khi dua len server
        public void UpdateNewRecordUpdate()
        {
            string query = string.Format("update StudentInClass set NewRecord = 0 where NewRecord = 2");
            cnn = connect.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            cmd.ExecuteNonQuery();
        } 

        public int RemoveStudentFromClass(string idSt, string IdCl)
        {
            string query = string.Format("Delete From StudentInClass where IDStudent ='{0}' and IDClass = '{1}'", idSt, IdCl);
            cnn = connect.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            return cmd.ExecuteNonQuery();
        }
    }
}
